import pandas as pd
from sqlalchemy import create_engine

# 1. 设备列表
device_list = ['GW161B'
]
placeholders = ','.join(f"'{i}'" for i in device_list)

# 2. 连接数据库
#engine = create_engine("mysql+pymysql://gwza_hgc:test_hgc@192.168.3.12:3306/gwza_hardware?charset=utf8mb4") #化工
#engine = create_engine("mysql+pymysql://tester:tester1234@192.168.3.132:3306/174dev?charset=utf8mb4")
engine = create_engine("mysql+pymysql://tester:tester1234@localhost:3306/gwza_hardware29?charset=utf8mb4")

# 3. 读取数据
query = f"""
SELECT  device_id,
        update_time AS time,
        JSON_UNQUOTE(JSON_EXTRACT(memo, '$.pa')) AS pa
FROM    map_device_his
WHERE   device_id IN ({placeholders})
AND     update_time BETWEEN '2024-09-27 00:00:00' AND '2024-09-28 00:00:00'
ORDER BY device_id, update_time
"""

df = pd.read_sql(query, engine)

# 4. 清洗脏数据
df['pa'] = df['pa'].astype(str).str.replace(r'(\d+)\.\d+\.', r'\1.', regex=True)
df['pa'] = pd.to_numeric(df['pa'], errors='coerce')-100000
df = df.dropna(subset=['pa'])

# 5. 透视：行=time，列=device_id，值=pa
pivot = df.pivot_table(index='time', columns='device_id', values='pa')

# 6. 仅保留所有设备都有值的时间点
pivot = pivot.dropna()

# 7. 写入 Excel（只带一列 time）
pivot.to_excel('pa_only_same_time_col.xlsx', index=True)

print('✅ 已生成 pa_only_same_time_col.xlsx')